﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;

namespace RBT.DataAccess {
    public class DAL {
        //ConfigurationManager lets one look at the web.config file,
        //here we are pulling out the connection string from that file to connect to the database


        static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GoDaddyConnection"].ToString());

       // static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyMvcDbConnectionString"].ToString());
        
        public static bool UserIsValid(string username, string password)
        {
            bool authenticated = false;


            string query = string.Format(("Select * FROM [User] WHERE Username = '{0}' AND Password ='{1}'"), username,
                                         password);

            SqlCommand cmd = new SqlCommand(query, conn); //uses the connection to run a query 

            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader(); 
            authenticated = sdr.HasRows; //if the query has rows, authenticated is set to true
            conn.Close();
            return authenticated;
        }

        public static void AddUserId(int employeeId, out MembershipCreateStatus status)
        {
            try
            {
                string query = string.Format(("Insert INTO [EmployeeId]\n VALUES ('{0}')"), employeeId);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                status = MembershipCreateStatus.ProviderError;
                return;
            }
            finally
            {
                conn.Close();
            }
            status = MembershipCreateStatus.Success;
           
        }

        public static void RemoveUser(int employeeId, out MembershipCreateStatus status)
        {
            try
            {
                string query = string.Format(("DELETE FROM [EmployeeId] WHERE EmployeeId = '{0}'"), employeeId);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                
            }
            catch (SqlException ex)
            {
                status = MembershipCreateStatus.InvalidQuestion;
                return;
            }
            finally
            {
                conn.Close();
            }
            try
            {
                string query = string.Format(("DELETE FROM [User] WHERE EmployeeId = '{0}'"), employeeId);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                status = MembershipCreateStatus.InvalidQuestion;
                return;
            }
            finally
            {
                conn.Close();
            }
            status = MembershipCreateStatus.Success;
        }


        public static bool EmployeeIdIsValid(int employeeId)
        {
            bool authenticated = false;

            string query = string.Format(("Select * FROM [EmployeeId] WHERE employeeId = '{0}' "), employeeId );

            SqlCommand cmd = new SqlCommand(query, conn); //uses the connection to run a query 

            conn.Open();
            SqlDataReader sdr = cmd.ExecuteReader();
            authenticated = sdr.HasRows; //if the query has rows, authenticated is set to true
            conn.Close();
            return authenticated;
        }

        public static void CreateAccount(int employeeId, string username, string password, string email, out MembershipCreateStatus status)
        {
            if (!EmployeeIdIsValid(employeeId))
            {
                status = MembershipCreateStatus.InvalidProviderUserKey;
                return;
            }

            try
            {
                string query = string.Format(("Insert INTO [User]\n VALUES ('{0}','{1}','{2}','{3}')"), username, password, email, employeeId);
                SqlCommand cmd = new SqlCommand(query, conn);
                conn.Open();

                cmd.ExecuteNonQuery();
                status = MembershipCreateStatus.Success;

            }
            catch (SqlException ex)
            {
                status = MembershipCreateStatus.ProviderError;
            }
            finally
            {
                conn.Close();
            }
           
        }
    }
}